<?php
/**
 * @author mwalters8@gmail.com
 * @version 1.1
 * @license Copyright (C) 2009  Matthew S. Walters
 * 
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 **/

/* {
========================================================
Execute the below SQL statement to setup the user table.
========================================================

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `userGroupKey` (
  `id` int(10) NOT NULL auto_increment,
  `creation_date` timestamp NOT NULL default '0000-00-00 00:00:00',
  `modification_date` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `users_id` int(10) NOT NULL default '0',
  `userGroups_id` int(10) NOT NULL default '0',
  `status` enum('Active','Inactive','Delete') NOT NULL default 'Active',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=432 DEFAULT CHARSET=utf8;


CREATE TABLE `userGroups` (
  `id` int(10) NOT NULL auto_increment,
  `creation_date` timestamp NOT NULL default '0000-00-00 00:00:00',
  `modification_date` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `groupName` varchar(255) NOT NULL default '',
  `status` enum('Active','Inactive','Delete') NOT NULL default 'Active',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=86 DEFAULT CHARSET=utf8;


CREATE TABLE `users` (
  `id` int(10) NOT NULL auto_increment,
  `creation_date` timestamp NOT NULL default '0000-00-00 00:00:00',
  `modification_date` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `username` varchar(255) NOT NULL default '',
  `password` varchar(255) NOT NULL default '',
  `status` enum('Active','Inactive','Delete') NOT NULL default 'Active',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=utf8;


SET FOREIGN_KEY_CHECKS = 1;

========================================================
END SQL STATEMENT
========================================================
} */

// Configure for your database
define('DB_SERVER', 'localhost'); // Server IP Address
define('DB_NAME', 'users'); // Name of database
define('DB_USER', 'users'); // Database Username
define('DB_PASSWORD', 'users'); // Database Password

class User {
	public $groups = array();
	public $id = '';

	function __construct($userId=null) {
		if ($userId != null) { $this->load($userId); } // Load data into User object
		return;
	}
	
	function __destruct() {
		return;
	}

	protected function query($sql) { // Execute SQL against database
		// Connect to the MySQL Server
		$dbLink = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD);
		If (!$dbLink) { // Could not connect to database server, return an error
			$queryResults['Error_Code'] = 101;
			$queryResults['Error_Message'] = 'Unable to connect to database server.';
			return($queryResults);
		}

		// Select the database
		$dbSelected = mysql_select_db(DB_NAME, $dbLink);
		If (!$dbSelected) { // Could not select database, return an error
			$queryResults['Error_Code'] = 102;
			$queryResults['Error_Message'] = 'Could not select database: ' . mysql_error();
			return($queryResults);
		}

		// Perform the query
		$queryResponse = mysql_query($sql);

		If (!$queryResponse) { // Could not perform query, return an error
			$queryResults['Error_Code'] = 103;
			$queryResults['Error_Message'] = 'Query Error: ' . mysql_error();
			return($queryResults);
		}

		If (substr($sql, 0, 6) == 'SELECT') {
			// Loop through results and add the recordset to Results_Array
			$recordCount = 1;
			while ($row = mysql_fetch_assoc($queryResponse)) {
				foreach ($row as $fieldName => $fieldValue) {
					$queryResults['Recordset'][$recordCount][$fieldName] = $fieldValue;
				}
				$recordCount++;
			}
			mysql_free_result($queryResponse);
		}

		// If it was an INSERT statement, then get the new Primary Key to return it
		If (substr($sql, 0, 6) == 'INSERT') {
			$queryResults['Inserted_ID'] = mysql_insert_id();
		}

		// If it was an UPDATE or DELETE statement, then get the number of rows that were affected to return it
		If (substr($sql, 0, 6) == 'UPDATE' || substr($sql, 0, 6) == 'DELETE') {
			$queryResults['Affected_Rows'] = mysql_affected_rows();
		}

		$queryResults['Error_Code'] = 0; // If we got to here, then there aren't any errors, so set error code to 0 and return the results
		$queryResults['Error_Message'] = '';
		mysql_close($dbLink);
		return($queryResults);
	}

	protected function prepare($string=null) { // Properly escape a string for MySQL
		if ($string != null) {
			$dbLink = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD) OR die(mysql_error());
			$string = mysql_real_escape_string($string);
		}
		return('"' . $string . '"');
	}

	public function load($userId) {
/*
	Load user data from database.  While the class comes with some basic fields available for the user, the idea is that
	the developer could add fields to the database as they see fit, and the class would 'learn' those fields and retrieve/save
	them when requested.  That is why SELECT * is used.

	So if the developer added a field called "petsName".  The class would retrieve this value and make it part of the user object:
	$user->petsName

	The class also understands the concept of user groups for access authentication purposes.  So you could assign a user to the group
	"admin" and then elsewhere test to see if the group is a member of "admin" before granting them access to functionality.

	The groups a user currently belongs to is retrieved when the load() method is called
*/

		$sql = 'SELECT * FROM users WHERE (id=' . $this->prepare($userId) . ') LIMIT 0,1;'; // Load user data from database
		$userData = $this->query($sql);
		if (count($userData['Recordset']) == 1) {
			foreach ($userData['Recordset'][1] as $field=>$value) {
				$this->$field = $value; // Build out user object
			}
		}

		$sql = 'SELECT groupName FROM userGroups LEFT JOIN userGroupKey ON userGroupKey.userGroups_id=userGroups.id LEFT JOIN users ON users.id=userGroupKey.users_id WHERE users.id=' . $this->prepare($this->id) . ';';
		$results = $this->query($sql);
		foreach ($results['Recordset'] as $k=>$group) {
			$addGroup = array($group);
			array_merge($this->groups, $addGroup); // Build out list of groups the user belongs to
		}
		return;
	}

	public function save() {
/*
	As with the load() method, save() has to respect the fact that the developer may have added fields to the user table.  So it has to dynamically
	build out its SQL statement and properly assign/remove a users relationship with groups.
*/

		if ($this->id == '') {
			$sql = 'SELECT id FROM users WHERE (username=' . $this->prepare($this->username) . ');';
			echo $sql;
			$results = $this->query($sql);
			if (array_key_exists('id', $results['Recordset'][1])) {
				$returnMsg['error'] = 101;
				$returnMsg['message'] = 'User already exists';
				return $returnMsg;
			}

			$sql = 'INSERT INTO users (creation_date, ';
			foreach ($this as $field=>$value) {
				if ($field != 'id' && $field != 'groups') { $sql .= $field . ', '; }
			}
			$sql = substr($sql, 0, (strlen($sql) - 2));
			$sql .= ') VALUES (' . $this->prepare(date('Y-m-d H:i:s', time())) . ', ';
			foreach ($this as $field=>$value) {
				if ($field != 'id' && $field != 'groups') { $sql .= $this->prepare($value) . ', '; }
			}
			$sql = substr($sql, 0, (strlen($sql) - 2));
			$sql .= ');';
			$results = $this->query($sql);
			if (array_key_exists('Inserted_ID', $results)) { $this->id = $results['Inserted_ID']; }
		} else {
			// Build out UPDATE statement for users record in the table
			$sql = 'UPDATE users SET ';
			foreach ($this as $field=>$value) {
				if ($field != 'id' && $field != 'groups') { $sql .= $field . '=' . $this->prepare($value) . ', '; }
			}
			$sql = substr($sql, 0, (strlen($sql) - 2));
			$sql .= ' WHERE (id='.$this->id.');';
			$this->query($sql);
		}

		// Instead of trying to figure out which groups they are still in and which ones they are no longer in, just clear the list and rebuild
		$sql = 'DELETE FROM userGroupKey WHERE users_id=(' . $this->prepare($this->id) . ');';
		$this->query($sql);

		// Loop through list of groups currently assigned to the user object and commit them to the database
		foreach ($this->groups as $groupName) {
			$groupId = '';
			$userGroupKeyId = '';

			// Determine the ID of the provided group name so a relationship can be established
			$sql = 'SELECT id FROM userGroups WHERE (groupName=' . $this->prepare($groupName) . ');';
			$results = $this->query($sql);
			if (array_key_exists('id', $results['Recordset'][1])) {
				$groupId = $results['Recordset'][1]['id'];
			}

			// If the group doesn't exist, add it and get the ID of the group name
			if ($groupId == '') {
				$sql = 'INSERT INTO userGroups (groupName) VALUES (' . $this->prepare($groupName) . ');';
				$results = $this->query($sql);
				$groupId = $results['Inserted_ID'];
			}

			// Sanity check, make sure the developer does try to add the user to the same group twice.
			$sql = 'SELECT id FROM userGroupKey WHERE (users_id=' . $this->prepare($this->id) . ' AND userGroups_id=' . $this->prepare($groupId) . ');';
			$results = $this->query($sql);
			if (array_key_exists('Recordset', $results)) {
				$userGroupKeyId = $results['Recordset'][1]['id'];
			}

			// Create relationship between user and user group in database
			if ($userGroupKeyId == '') {
				$sql = 'INSERT INTO userGroupKey (users_id, userGroups_id) VALUES (' . $this->prepare($this->id) . ', ' . $this->prepare($groupId) . ');';
				$this->query($sql);
			}
		}
		$this->load($this->id);
		return;
	}

	public function delete() { // Delete user from system
		$sql = 'DELETE FROM users WHERE (id=' . $this->prepare($this->id) . ');';
		$this->query($sql);
		$sql = 'DELETE FROM userGroupKey WHERE (users_id=' . $this->prepare($this->id) . ');';
		$this->query($sql);
		return;
	}

}

?>